﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Collections.Specialized;
using MakeOracleConnection.Properties;

namespace MakeOracleConnection
{
    class Program
    {

        static void Main(string[] args)
        {
            OracleConnection Conn;
            Console.WriteLine("*********************************************");
            Console.WriteLine("*******************APP SETTINGS**************");
            Console.WriteLine(Settings.Default.connectionString); 
            Console.WriteLine(Properties.Settings.Default.connectionString.ToString());
            Console.WriteLine(DateTime.Now.ToShortDateString());
            Console.WriteLine("*********************************************");
            string strCon = Settings.Default.connectionString;
            Conn = new OracleConnection(strCon);
            Conn.Open();
            Console.WriteLine(Conn.State);
            InsertRecord(Conn);
            CallStoredProcedure(Conn);
        }
        static int InsertRecord(OracleConnection Conn)
        {
            OracleCommand oCmd = new OracleCommand();
            oCmd.CommandType = System.Data.CommandType.Text; 
            oCmd.CommandText = "INSERT INTO TEMP VALUES('SA-"+ System.DateTime.Now.ToString() + "')";
            oCmd.Connection = Conn;
            oCmd.ExecuteNonQuery();
            return 0;
        }

        /*Implement Transaction Processing*/

        static void CallStoredProcedure(OracleConnection iConn)
        {
            OracleCommand oCmd;
            OracleParameter oPrm;
            OracleTransaction oTrans;
            try
            {
                oCmd = new OracleCommand();
                oCmd.CommandType = System.Data.CommandType.StoredProcedure;
                oCmd.CommandText = "sp_addtotemp";
                oCmd.Connection = iConn;
                oTrans = iConn.BeginTransaction();
                oPrm = oCmd.CreateParameter();
                oPrm.ParameterName = "v_Opt";
                oPrm.OracleDbType = OracleDbType.Varchar2;
                oPrm.Direction = System.Data.ParameterDirection.Input;
                oPrm.Size = 10;
                oPrm.Value = DateTime.UtcNow.ToString();
                oCmd.Parameters.Add(oPrm);

                oPrm = oCmd.CreateParameter();
                oPrm.ParameterName = "v_Err";
                oPrm.OracleDbType = OracleDbType.Varchar2;
                oPrm.Direction = System.Data.ParameterDirection.Output; 
                oPrm.Size = 200;
                oCmd.Parameters.Add(oPrm);
                oCmd.ExecuteNonQuery();
                if (oPrm.Value.ToString() == "")
                {
                    oTrans.Commit();
                }
                else
                {
                    Console.WriteLine(oPrm.Value);
                    oTrans.Rollback();
                }
            }
            catch(Exception x)
            {
                Console.WriteLine(x.ToString());
            }
            finally
            {
                Console.WriteLine("Finally!");
            }
        }
    }
    }
